﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using RealtorsPortal.Data_Access_Layer;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace RealtorsPortal.AdminCP
{
    public partial class Statistics : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!IsPostBack){
                this.LoadAndBindData();
            }
        }

        private void LoadAndBindData()
        {
            SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["myconnection"].ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            cmd.CommandText = @"SELECT count(AdvId) from Advertisements";
            lblNumberOfAds.Text = cmd.ExecuteScalar().ToString();

            cmd.CommandText = @"SELECT count(AdvId) from Advertisements where IsActive = 'True'";
            lblNumberOfActive.Text = cmd.ExecuteScalar().ToString();

            cmd.CommandText = @"SELECT count(UserId) from Users as u, Roles as r where u.RoleId = r.RoleId and r.RoleName ='Private Seller'";
            lblNumberOfSellers.Text = cmd.ExecuteScalar().ToString();

            cmd.CommandText = @"SELECT count(UserId) from Users as u, Roles as r where u.RoleId = r.RoleId and r.RoleName ='Agent'";
            lblNumberOfAgents.Text = cmd.ExecuteScalar().ToString();

            cmd.CommandText = @"SELECT count(CategoryId) from Categories";
            lblNumberOfCategories.Text = cmd.ExecuteScalar().ToString();

            cmd.CommandText = @"SELECT count(PackageId) from Packages";
            lblNumberOfPackages.Text = cmd.ExecuteScalar().ToString();

            cmd.CommandText = @"SELECT count(AdvId) from Advertisements as a, Packages as p where a.PackageId = p.PackageId and p.Price <> 0";
            string numOfPaid = cmd.ExecuteScalar().ToString();
            if (numOfPaid == "") numOfPaid = "0";
            lblNumberOfPaid.Text = numOfPaid;

            cmd.CommandText = @"SELECT sum(p.Price) from Subscription as s, Packages as p where s.PackageId = p.PackageId and p.Price <> 0";
            lblTotal.Text = cmd.ExecuteScalar().ToString() + " " + WebConfigurationManager.AppSettings["currency"].ToString();

            cmd.CommandText = "SELECT sum(p.Price) as 'Total' from Subscription as s, Packages as p where s.PackageId = p.PackageId and p.Price <> 0 and s.SubDate = '"+DateTime.Now.ToShortDateString()+"'";
            string paidtoday = cmd.ExecuteScalar().ToString();
            if (paidtoday == "") paidtoday = "0";
            lblPaymentReceivedToday.Text = paidtoday + " " + WebConfigurationManager.AppSettings["currency"].ToString();
            conn.Close();

            lblNumberOfDeactive.Text = (int.Parse(lblNumberOfAds.Text) - int.Parse(lblNumberOfActive.Text)).ToString();
            
        }
    }
}